package com.sf.sms.utils;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import org.xml.sax.SAXException;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

public class AutoConstructionUtil {
	Connection conn = null;
	Statement stmt = null;

	public AutoConstructionUtil()
			throws ParserConfigurationException, SAXException, IOException, ClassNotFoundException, SQLException {
		String url = "jdbc:mysql://localhost:3306/sms";
		String username = "root";
		String password = "12345";
		String driverClassName = "com.mysql.jdbc.Driver";

		Class.forName(driverClassName);

		this.conn = DriverManager.getConnection(url, username, password);
		this.stmt = conn.createStatement();
	}

	// 格式化，从db模式到驼峰模式
	private static String formatDBmodeToHumpMode(String tableName) {
		String modularName = "";
		if (tableName.indexOf("ms_") > -1) {
			tableName = tableName.substring(tableName.indexOf("ms_") + 3);
		}
		if(tableName.indexOf("mu_") > -1){
			tableName = tableName.substring(tableName.indexOf("mu_") + 3);
		}
		
		for (String str : tableName.split("_")) {
			str = str.substring(0, 1).toUpperCase() + str.substring(1).toLowerCase();
			modularName = modularName + str;
		}
		return modularName;
	}

	// 首字母小写
	private String formatFirstCharToLowerCase(String str) {
		return str.substring(0, 1).toLowerCase() + str.substring(1);
	}

	// 自动构建之前全表扫描，统计需要构建的表和需要构建的字段
	private List<Map<String, String>> beforeAutoGenerate() throws SQLException {
		String sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES where table_schema = 'sms'";
		ResultSet rs = stmt.executeQuery(sql);
		List<String> allTableList = Lists.newArrayList();
		List<Map<String, String>> allList = Lists.newArrayList();
		while (rs.next()) {
			Map<String, String> map = Maps.newHashMap();
			map.put("tableName", rs.getString(1));
			map.put("comment", rs.getString(2));
			allTableList.add(rs.getString(1));
			allList.add(map);
		}
		sql = "select table_name,is_generated from test.tb_auto_generate_tables_config";
		rs = stmt.executeQuery(sql);
		List<String> hasGeneratedTableList = Lists.newArrayList();
		Map<String, Map<String, String>> hasGeneratedMap = Maps.newHashMap();
		while (rs.next()) {
			hasGeneratedTableList.add(rs.getString(1));
			Map<String, String> map = Maps.newHashMap();
			map.put("tableName", rs.getString(1));
			map.put("isGenerated", rs.getString(2));
			hasGeneratedMap.put(rs.getString(1),map);
		}
		List<Map<String, String>> needGeneratedTableList = Lists.newArrayList();
		for (int i = 0; i < allTableList.size(); i++) {
			String tableName = allTableList.get(i);
			if (!hasGeneratedTableList.contains(tableName)) {
				sql = "insert into test.tb_auto_generate_tables_config(table_name,is_generated,create_tm,create_emp,update_tm,update_emp) "
						+ " values('" + tableName + "','0',now(),'admin',now(),'admin')";
				stmt.executeUpdate(sql);
				needGeneratedTableList.add(allList.get(i));
			} else {
				Map<String, String> map = hasGeneratedMap.get(tableName);
				String isGenerated = map.get("isGenerated");
				String columnSql = "show full fields from " + tableName;
				rs = stmt.executeQuery(columnSql);
				int dbCnt = 0;
				List<String> dbList = Lists.newArrayList();
				while (rs.next()) {
					dbCnt++;
					dbList.add(rs.getString(1));
				}
				columnSql = "select column_name from test.tb_auto_generate_tab_columns_config where table_name = '"
						+ tableName + "'";
				rs = stmt.executeQuery(columnSql);
				int generatedCnt = 0;
				List<String> generatedList = Lists.newArrayList();
				while (rs.next()) {
					generatedCnt++;
					generatedList.add(rs.getString(1));
				}
				if (dbCnt != generatedCnt || !(generatedList.containsAll(dbList)) || "0".equals(isGenerated)) {
					columnSql = "delete from test.tb_auto_generate_tab_columns_config where table_name = '" + tableName
							+ "'";
					stmt.executeUpdate(columnSql);
					columnSql = "update test.tb_auto_generate_tables_config set is_generated = '0' where table_name = '"
							+ tableName + "'";
					stmt.executeUpdate(columnSql);
					needGeneratedTableList.add(allList.get(i));
				}
			}
		}
		return needGeneratedTableList;
	}

	public void autoGenerateByTableName(String javaFilePath, String packageName)
			throws SQLException, ClassNotFoundException, IOException {
		List<Map<String, String>> needGeneratedTableList = beforeAutoGenerate();
		for (Map<String, String> map : needGeneratedTableList) {
			String tableName = map.get("tableName");
		
			//若存在实体类则跳过创建步骤，避免创建不需要的mpper、service、controller文件
			File file = new File(javaFilePath + "model\\" + formatDBmodeToHumpMode(tableName) + ".java");
			if(file.exists()) {
				continue;
				
			}
			
			String tableComment = map.get("comment");
			String sql = "show full fields from " + tableName;
			ResultSet rs = stmt.executeQuery(sql);
			List<Map<String, String>> list = Lists.newArrayList();
			while (rs.next()) {
				Map<String, String> columnMap = Maps.newHashMap();
				columnMap.put("field", rs.getString(1));
				columnMap.put("type", rs.getString(2));
				columnMap.put("comment", rs.getString(9));
				list.add(columnMap);
			}
			
			//往model文件夹中新建实体类
			writeModel(javaFilePath+"software\\entity\\", packageName, tableName.toLowerCase(), list, tableComment);
			//往mapper文件夹中新建mapper.java文件
			writeMapper(javaFilePath+"software\\dao\\", packageName, tableName.toLowerCase(), list);
			//往mapper文件夹中新建mapper.xml文件
			writeMapperXml(javaFilePath+"mapper\\", packageName, tableName.toLowerCase(), list);
			//往service文件夹中新建service文件
			writeService(javaFilePath+"software\\service\\", packageName, tableName.toLowerCase(), list);
			//往controller文件夹中新建controller文件
			writeController(javaFilePath+"software\\action\\", packageName, tableName.toLowerCase(), list);
			
			sql = "delete from test.tb_auto_generate_tab_columns_config where table_name = '" + tableName + "'";
			stmt.executeUpdate(sql);
			for (Map<String, String> columnMap : list) {
				sql = "insert into test.tb_auto_generate_tab_columns_config (table_name,column_name,create_tm,create_emp,update_tm,update_emp)"
						+ " values('" + tableName + "','" + columnMap.get("field") + "',now(),'admin',now(),'admin')";
				stmt.executeUpdate(sql);
			}
			sql = "update test.tb_auto_generate_tables_config set is_generated = 1 where table_name = '" + tableName
					+ "'";
			stmt.executeUpdate(sql);
		}
		conn.close();
	}
	
	//新建controller文件
	private void writeController(String javaFilePath, String packageName, String tableName, List<Map<String, String>> list)
			throws IOException, ClassNotFoundException, SQLException {
		String humpModeStr = formatDBmodeToHumpMode(tableName);
		File file = new File(javaFilePath + humpModeStr + "Action.java");
		if(file.exists()) {
			return;
		}
		
		//新建service.java文件
		FileWriter fileWriter = new FileWriter(javaFilePath + humpModeStr + "Action.java");
		StringBuffer contentTxt = new StringBuffer();
		contentTxt.append("package " + packageName + ".software.action;\n");
		contentTxt.append("\n");
		contentTxt.append("import javax.servlet.http.HttpServletRequest;\n");
		contentTxt.append("import javax.servlet.http.HttpServletResponse;\n");
		contentTxt.append("\n");
		
		contentTxt.append("import org.springframework.beans.factory.annotation.Autowired;\n");
		contentTxt.append("import org.springframework.stereotype.Controller;\n");
		contentTxt.append("import org.springframework.web.bind.annotation.RequestMapping;\n");
		contentTxt.append("import org.springframework.web.bind.annotation.RequestMethod;\n");
		contentTxt.append("import org.springframework.web.bind.annotation.ResponseBody;\n");
		contentTxt.append("\n");
		
		contentTxt.append("import com.sf.sms.software.service." + humpModeStr + "Service;\n");
		contentTxt.append("\n");
		contentTxt.append("@Controller\n");
		contentTxt.append("public class " + humpModeStr + "Action {\n");
		contentTxt.append("\n");
		contentTxt.append("	@Autowired\n");
		contentTxt.append("	private " + humpModeStr + "Service " + formatFirstCharToLowerCase(humpModeStr) + "Service;\n");
		contentTxt.append("\n");
		contentTxt.append("	@ResponseBody\n");
		contentTxt.append("	@RequestMapping(value = \"/save" + humpModeStr + "\", method = RequestMethod.POST)\n");
		contentTxt.append("	public String save" + humpModeStr + "(HttpServletRequest request, HttpServletResponse response) throws Exception {\n");
		contentTxt.append("\n");
		contentTxt.append("		return null;\n");
		contentTxt.append("	}\n");
		contentTxt.append("\n");
		contentTxt.append("}\n");
		contentTxt.append("\n");
		
		fileWriter.write(contentTxt.toString());
		fileWriter.close();
		System.out.println("success " + javaFilePath + humpModeStr + "Action.java");
	}
	
	//新建Service文件
	private void writeService(String javaFilePath, String packageName, String tableName, List<Map<String, String>> list)
			throws IOException, ClassNotFoundException, SQLException {
		String humpModeStr = formatDBmodeToHumpMode(tableName);
		File file = new File(javaFilePath + humpModeStr + "Service.java");
		if(file.exists()) {
			return;
		}
		
		//新建service.java文件
		FileWriter fileWriter = new FileWriter(javaFilePath + humpModeStr + "Service.java");
		StringBuffer contentTxt = new StringBuffer();
		
		contentTxt.append("package " + packageName + ".software.service;\n");
		contentTxt.append("\n");
		contentTxt.append("import org.springframework.beans.factory.annotation.Autowired;\n");
		contentTxt.append("import org.springframework.stereotype.Service;\n");
		contentTxt.append("\n");
		contentTxt.append("import com.sf.sms.software.dao." + humpModeStr + "Mapper;\n");
		contentTxt.append("import com.sf.sms.software.entity." + humpModeStr + ";\n");
		contentTxt.append("\n");
		contentTxt.append("@Service\n");
		contentTxt.append("public class " + humpModeStr + "Service {\n");
		contentTxt.append("\n");
		contentTxt.append("	@Autowired\n");
		contentTxt.append("	private " + humpModeStr + "Mapper " + formatFirstCharToLowerCase(humpModeStr) + "Mapper;\n");
		contentTxt.append("\n");
		contentTxt.append("	public void add" + humpModeStr + "(" + humpModeStr + " " + humpModeStr.toLowerCase() +") {\n");
		contentTxt.append("		" + formatFirstCharToLowerCase(humpModeStr) + "Mapper.add" + humpModeStr +"(" + humpModeStr.toLowerCase() + ");\n");
		contentTxt.append("	}\n");
		contentTxt.append("\n");
		contentTxt.append("}\n");
		
		fileWriter.write(contentTxt.toString());
		fileWriter.close();
		System.out.println("success " + javaFilePath + humpModeStr + "Service.java");
	}
	

	// 新建mpper文件
	private void writeMapper(String javaFilePath, String packageName, String tableName, List<Map<String, String>> list)
			throws IOException, ClassNotFoundException, SQLException {
		String humpModeStr = formatDBmodeToHumpMode(tableName);
		File file = new File(javaFilePath + humpModeStr + "Mapper.java");
		if(file.exists()) {
			return;
		} 
		
		//新建mpper.java文件
		FileWriter fileWriter = new FileWriter(javaFilePath + humpModeStr + "Mapper.java");
		StringBuffer contentTxt = new StringBuffer();
		
		contentTxt.append("package " + packageName + ".software.dao;\n");
		contentTxt.append("\n");
		contentTxt.append("import com.sf.sms.software.entity." + humpModeStr + ";\n");
		contentTxt.append("\n");
		contentTxt.append("public interface " + humpModeStr + "Mapper {\n");
		contentTxt.append("\n");
		contentTxt.append("	public abstract void add" + humpModeStr + "(" + humpModeStr + " " + humpModeStr.toLowerCase() + ");\n");
		contentTxt.append("\n");
		contentTxt.append("}\n");
		
		fileWriter.write(contentTxt.toString());
		fileWriter.close();
		System.out.println("success " + javaFilePath + humpModeStr + "Mapper.java");
	}
	
	// 新建mpper文件
	private void writeMapperXml(String javaFilePath, String packageName, String tableName, List<Map<String, String>> list)
				throws IOException, ClassNotFoundException, SQLException {
			String humpModeStr = formatDBmodeToHumpMode(tableName);
			File file = new File(javaFilePath + humpModeStr + "Mapper.xml");
			if(file.exists()) {
				return;
			}
			
			//新建mapper.xml文件
			FileWriter fileWriterXml = new FileWriter("C:\\Users\\80002018\\git\\softwaremanagement\\src\\main\\resources\\mapper\\software\\"+ humpModeStr + "Mapper.xml");
			StringBuffer contentXml = new StringBuffer();
			contentXml.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
			contentXml.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \n");
			contentXml.append("\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n");
			contentXml.append("\n");
			contentXml.append("<mapper namespace=\"com.sf.sms.software.dao." + humpModeStr + "Mapper" + "\">\n");
			contentXml.append("\n");
			
			//生成resultMap
			contentXml.append("    <resultMap type=\"com.sf.sms.software.entity." + humpModeStr + "\" id=\"" + formatFirstCharToLowerCase(humpModeStr) + "ResMap\">\n");
			for(Map<String, String> map : list) {
				String columnStr = map.get("field");
				
				if("id".equals(columnStr)) {
					contentXml.append("        <id property=\"id\" column=\"id\"/>\n");
				} else {
					String humpStr = formatDBmodeToHumpMode(columnStr);
					String parameterStr = formatFirstCharToLowerCase(humpStr);
					contentXml.append("        <result property=\"" + parameterStr + "\" column=\"" + columnStr + "\"/>\n");
				}
			}
			contentXml.append("    </resultMap>\n");
			
			contentXml.append("\n");
			
			//生成插入语句
			contentXml.append("    <insert id=\"add" +humpModeStr + "\" parameterType=\"com.sf.sms.software.entity." + humpModeStr + "\"\n");
			contentXml.append("        useGeneratedKeys=\"true\" keyProperty=\"id\">\n");
			contentXml.append("      insert into\n");
			contentXml.append("      	" + tableName + "(");
			
			int i = 1;
			for(Map<String, String> map : list) {
				if("id".equals(map.get("field"))) {
					i++;
					continue;
				}
				
				if(list.size() == i) {
					contentXml.append(map.get("field") + ")\n");
				} else {
					if(i % 8 == 0) {
						contentXml.append(map.get("field") + ",\n");
						contentXml.append("      				");
					} else {
						contentXml.append(map.get("field") + ",");
					}
				}
				i++;
			}
			
			contentXml.append("      values (\n");
			
			i = 1;
			for(Map<String, String> map : list) {
				if("id".equals(map.get("field"))) {
					i++;
					continue;
				}
				
				String columnStr = map.get("field");

				String humpStr = formatDBmodeToHumpMode(columnStr);
				String parameterStr = formatFirstCharToLowerCase(humpStr);
				
				if(list.size() == i) {
					//时间类型特殊处理： timestamp/date
					if(map.get("type").contains("timestamp") || map.get("type").contains("date")) {
						contentXml.append(" now())\n");
					} else {
						contentXml.append(" #{" + parameterStr + "})\n");
					}
				} else {
					if(2 == i) {
						contentXml.append("      	");	
					}
					if(map.get("type").contains("timestamp") || map.get("type").contains("date")) {
						contentXml.append(" now(),");
					} else {
						contentXml.append(" #{" + parameterStr + "},");
					}
					
					if(0 == i % 8) {
						contentXml.append("\n");
						contentXml.append("      	");
					}
				}
				i++;
			}
			
			contentXml.append("    </insert>\n");
			contentXml.append("\n");
			contentXml.append("</mapper>");
			
			fileWriterXml.write(contentXml.toString());
			fileWriterXml.close();
			System.out.println("success " + javaFilePath + humpModeStr + "Mapper.xml");
		}
	

	// 根据表名写domain
	private void writeModel(String javaFilePath, String packageName, String tableName, List<Map<String, String>> list, String tableComment)
			throws IOException, ClassNotFoundException, SQLException {
		String humpModeStr = formatDBmodeToHumpMode(tableName);
		File file = new File(javaFilePath + humpModeStr + ".java");
		if(file.exists()) {
			return;
		}
		System.out.println(javaFilePath);
		FileWriter fileWriter = new FileWriter(javaFilePath + humpModeStr + ".java");
		System.out.println(fileWriter);
		StringBuffer contentTxt = new StringBuffer();
		
		contentTxt.append("package " + packageName + ".software.entity;\n");
		contentTxt.append("\n");
		contentTxt.append("import java.util.Date;\n");
		contentTxt.append("\n");
		contentTxt.append("\n");
		
		contentTxt.append("/**\n");
		contentTxt.append(" * \n");
		contentTxt.append(" * " + tableComment + "\n");
		contentTxt.append(" * \n");
		contentTxt.append(" */\n");
		contentTxt.append("public class " + humpModeStr + " {\n");
		contentTxt.append("\n");
		for (Map<String, String> map : list) {
			String columnStr = map.get("field");

			String humpStr = formatDBmodeToHumpMode(columnStr);
			String parameterStr = formatFirstCharToLowerCase(humpStr);
			contentTxt.append("	private " + getParameterType(map.get("type")) + " " + parameterStr + ";\n");
			contentTxt.append("\n");
		}
		for (Map<String, String> map : list) {
			String columnStr = map.get("field");
			String humpStr = formatDBmodeToHumpMode(columnStr);
			String parameterStr = formatFirstCharToLowerCase(humpStr);
			contentTxt.append("	public " + getParameterType(map.get("type")) + " get" + humpStr + "() {\n");
			contentTxt.append("		return " + parameterStr + ";\n");
			contentTxt.append("	}\n");
			contentTxt.append("\n");
			contentTxt.append("	public void set" + humpStr + "(" + getParameterType(map.get("type")) + " " + parameterStr + ") {\n");
			contentTxt.append("		this." + parameterStr + " = " + parameterStr + ";\n");
			contentTxt.append("	}\n");
			contentTxt.append("\n");
		}
		contentTxt.append("}\n");
		fileWriter.write(contentTxt.toString());
		fileWriter.close();
		System.out.println("success " + javaFilePath + humpModeStr + ".java");
	}
	
	//参数类型转换
	private String getParameterType(String columnType) {
		String pt = "String";
		if(columnType.contains("bigint")) {
			pt = "Long";
		} else if(columnType.contains("timestamp") || columnType.contains("date")) {
			pt = "Date";
		} else if(columnType.contains("tinyint(1)")) {
			pt = "Boolean";
		} else if(columnType.contains("tinyint")) {
			pt = "Integer";
		}
		
		return pt;
	}
}
